﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace SelectProductsAndCategories
{
    class ProductsAndCategories
    {
        static SqlConnection OpenDataBaseConnection()
        {
            SqlConnection connection = new SqlConnection("Server=.\\SQL_SERVER_2008;"+
            "Database = Northwind; Integrated Security = true");
            connection.Open();
            return connection;
        }

        static void PrintAllProductsAndCategories(SqlConnection connection)
        {
            string command = @"SELECT c.CategoryName,
                        p.ProductName
                        FROM Products p
                        JOIN Categories c on c.CategoryID = p.CategoryID
                        GROUP BY c.CategoryName, p.ProductName";

            SqlCommand selectAllProductsAndCategories = new SqlCommand(command, connection);
            SqlDataReader reader = selectAllProductsAndCategories.ExecuteReader();

            using (reader)
            {
                while (reader.Read())
                {
                    string category = (string)reader["CategoryName"];
                    string productName = (string)reader["ProductName"];
                    Console.WriteLine("{0} -> {1}",productName, category);
                }
            }
        }

        static int AddProduct(SqlConnection connection, string productName, int supplierId, int categoryId, string quantityPerUnit,
            decimal unitPrice, int unitsInStock, int unitsOnOrder, int reorderLevel, int discontinued)
        {
            string command = "INSERT INTO Products(ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock," +
                "UnitsOnOrder, ReorderLevel, Discontinued)" +
                "VALUES(@productName,@supplierId, @categoryId, @quantityPerUnit, @unitPrice, @unitsInStock, @unitsOnOrder,"+
                "@reorderLevel, @discontinued)";
            SqlCommand insertNewProduct = new SqlCommand(command, connection);
            insertNewProduct.Parameters.Add(new SqlParameter("@productName", productName));
            insertNewProduct.Parameters.Add(new SqlParameter("@supplierId", supplierId));
            insertNewProduct.Parameters.Add(new SqlParameter("@categoryId", categoryId));
            insertNewProduct.Parameters.Add(new SqlParameter("@quantityPerUnit", quantityPerUnit));
            insertNewProduct.Parameters.Add(new SqlParameter("@unitPrice", unitPrice));
            insertNewProduct.Parameters.Add(new SqlParameter("@unitsInStock", unitsInStock));
            insertNewProduct.Parameters.Add(new SqlParameter("@unitsOnOrder", unitsOnOrder));
            insertNewProduct.Parameters.Add(new SqlParameter("@reorderLevel", reorderLevel));
            insertNewProduct.Parameters.Add(new SqlParameter("@discontinued", discontinued));

            int affectedRows = insertNewProduct.ExecuteNonQuery();

            return affectedRows;
        }

        static void Main(string[] args)
        {
            SqlConnection conneciton = OpenDataBaseConnection();
            using (conneciton)
            {
                //PrintAllProductsAndCategories(conneciton);
                AddProduct(conneciton, "Keyboard Pesho", 2, 3, "1 in one box", 350, 2, 3, 5, 2);
            }
        }
    }
}
